using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.Sql.SchemaModel;
using Microsoft.Data.Schema.Sql;
using Microsoft.Data.Schema.StaticCodeAnalysis;
using System.Text.RegularExpressions;

using System.Linq;

namespace Neznayka
{
    /// <summary>
    /// This is a SQL rule which returns a warning message 
    /// whenever there is a ........................................
    /// This rule only applies to .................................
    /// </summary>

    [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
    [DataRuleAttribute(
        NeznaykaConstants.NameSpace,
        NeznaykaConstants.EnforceIndexKeyColumnSeparationRuleId,
        NeznaykaConstants.ResourceBaseName,
        NeznaykaConstants.EnforceIndexKeyColumnSeparation_RuleName,
        NeznaykaConstants.CategoryDatabaseStructures,
        DescriptionResourceId = NeznaykaConstants.EnforceIndexKeyColumnSeparation_ProblemDescription)]
    //[SupportedElementType(typeof(ISqlProcedure))]
    //[SupportedElementType(typeof(ISqlTrigger))]
    //[SupportedElementType(typeof(ISqlFunction))]
    //[SupportedElementType(typeof(ISqlView))]
    //[SupportedElementType(typeof(ISqlTable))]

    [SupportedElementType(typeof(ISqlIndex))]
//    [SupportedElementType(typeof(ISqlPrimaryKeyConstraint))]
//    [SupportedElementType(typeof(ISqlUniqueConstraint))]

    public class EnforceIndexKeyColumnSeparationRule : StaticCodeAnalysisRule
    {
        #region Overrides
        /// <summary>
        /// Analyze the model element
        /// </summary>
        public override IList<DataRuleProblem> Analyze(DataRuleSetting ruleSetting, DataRuleExecutionContext context)
        {
            // (Re)-Load Environment settings

            List<DataRuleProblem> problems;
            SqlSchemaModel sqlSchemaModel;
            ISqlModelElement sqlElement;
            TSqlFragment sqlFragment;
            DMVRuleSetup.RuleSetup(context, out problems, out sqlSchemaModel, out sqlElement, out sqlFragment);

            // Refresh cached index/constraints/tables lists from Model
            DMVRuleSetup.RefreshDDLCache(sqlSchemaModel);

            // Get Database Schema and name of this model element.
            string selfSchema = sqlElement.Name.Parts[0];
            //string selfOwningObjectName = sqlElement.Name.Parts[1];
            string selfName   = sqlElement.Name.Parts[2];

            ISqlIndex self = sqlElement as ISqlIndex;
            // if it's a 2005+ index, also check the included columns
            ISql90Index self90IX = sqlElement as ISql90Index;

            List<TSqlFragment> issues = new List<TSqlFragment>();

            // Not the best named rule.  
            // The intent is to avoid having indexes that have a leading edge edge that is subsumed by another index.
            // or unique or primary key constraint.
            // The checked index must have no included columns. (There's another rule for that.)
            // The checked index must also not consist of a strict permutation of all the columns of another index
            // The reasons for this come down to an article on Oracle I can't remember the details of.
            if (self != null && (self90IX == null || self90IX.IncludedColumns == null || self90IX.IncludedColumns.Count == 0))
            {
                String owningObjectSchema = self.IndexedObject.Name.Parts[0];
                String owningObjectTable = self.IndexedObject.Name.Parts[1];

                List<ISqlPrimaryKeyConstraint> pks = ModelIndexAndKeysUtils.getPrimaryKeys(owningObjectSchema, owningObjectTable);
                List<ISqlIndex> indexes = ModelIndexAndKeysUtils.getIndexes(owningObjectSchema, owningObjectTable);
                List<ISqlUniqueConstraint> uniqueConstraints = ModelIndexAndKeysUtils.getUniqueConstraints(owningObjectSchema, owningObjectTable);

                List<String> LeadingEdgeIndexColumns = new List<String>();
                foreach (var c in self.ColumnSpecifications)
                {
                    String lastElement = "";
                    foreach (var n in c.Column.Name.Parts)
                    {
                        lastElement = n;
                    }
                    LeadingEdgeIndexColumns.Add(lastElement);
                }
                bool foundMoreInclusiveIndex = false;
                foreach (var v in pks)
                {
                    // if this 'index' isn't the index underlying the pk, check it.
                    // is this right/needed/wasted effort ?
                    if ( v.Name == null || ! (  SqlComparer.CompareEqual(v.Name.Parts[0], selfSchema)
                                                && SqlComparer.CompareEqual(v.Name.Parts[1], selfName)
                                                )
                        )
                    {
                        List<String> PKLeadingEdgeIndexColumns = new List<String>();
                        foreach (var c in v.ColumnSpecifications)
                        {
                            String lastElement = "";
                            foreach (var n in c.Column.Name.Parts)
                            {
                                lastElement = n;
                            }
                            PKLeadingEdgeIndexColumns.Add(lastElement);
                        }
                        foundMoreInclusiveIndex = determineIfThisIndexIsSubsumedByTheOtherIndex(LeadingEdgeIndexColumns, PKLeadingEdgeIndexColumns);
                        if (foundMoreInclusiveIndex)
                        {
                            break;
                        }
                    }
                }
                if (!foundMoreInclusiveIndex)
                {
                    foreach (var v in indexes)
                    {
                        // if this 'index' isn't the index we're checking - check it.
                        if (!(SqlComparer.CompareEqual(v.Name.Parts[0], selfSchema)
                               && SqlComparer.CompareEqual(v.Name.Parts[1], owningObjectTable)
                               && SqlComparer.CompareEqual(v.Name.Parts[2], selfName)
                               )
                            )
                        {
                            List<String> OtherLeadingEdgeIndexColumns = new List<String>();
                            foreach (var c in v.ColumnSpecifications)
                            {
                                String lastElement = "";
                                foreach (var n in c.Column.Name.Parts)
                                {
                                    lastElement = n;
                                }
                                OtherLeadingEdgeIndexColumns.Add(lastElement);
                            }
                            foundMoreInclusiveIndex = determineIfThisIndexIsSubsumedByTheOtherIndex(LeadingEdgeIndexColumns, OtherLeadingEdgeIndexColumns);
                            if (foundMoreInclusiveIndex)
                            {
                                break;
                            }
                        }
                    }

                }
                if (!foundMoreInclusiveIndex)
                {
                    foreach (var v in uniqueConstraints)
                    {
                        // if this 'index' isn't the index we're checking - check it.
                        if (v.Name == null || !(SqlComparer.CompareEqual(v.Name.Parts[0], selfSchema)
                                                    && SqlComparer.CompareEqual(v.Name.Parts[1], selfName)
                                                    )
                            )
                        {
                            List<String> ConstraintLeadingEdgeIndexColumns = new List<String>();
                            foreach (var c in v.ColumnSpecifications)
                            {
                                String lastElement = "";
                                foreach (var n in c.Column.Name.Parts)
                                {
                                    lastElement = n;
                                }
                                ConstraintLeadingEdgeIndexColumns.Add(lastElement);
                            }
                            foundMoreInclusiveIndex = determineIfThisIndexIsSubsumedByTheOtherIndex(LeadingEdgeIndexColumns, ConstraintLeadingEdgeIndexColumns);
                            if (foundMoreInclusiveIndex)
                            {
                                break;
                            }
                        }
                    }
                }
                if (foundMoreInclusiveIndex)
                {
                    issues.Add(sqlFragment);
                }

                // Create problems for each object
                foreach (TSqlFragment issue in issues)
                {
                    DataRuleProblem problem = new DataRuleProblem(this,
                                                String.Format(CultureInfo.CurrentCulture, this.RuleProperties.Description, SqlRuleUtils.GetElementName(sqlSchemaModel, sqlElement)),
                                                sqlElement);

                    SqlRuleUtils.UpdateProblemPosition(problem, issue.StartOffset, issue.FragmentLength);
                    problems.Add(problem);
                }
            }
            return problems;
        }

        private static bool determineIfThisIndexIsSubsumedByTheOtherIndex(List<String> TheseKeysColumns, List<String> TheOtherKeysColumns)
        {
            bool foundIndexThatMatchesAKey = false;

            List<Int32> allPos          = ModelIndexAndKeysUtils.getCorrespondingKeyPositions(TheseKeysColumns, TheOtherKeysColumns);
            // matchedPos lists the columns in TheseKeysColumns that were actually found in TheseKeysColumns
            List<Int32> matchedPos      = allPos.Where(n => n != -1).Select(n => n).ToList();

            // If this index is just a restatement of another index 
            // same columns in potentially different order
            // it's irrelevant
            // else if its a a strict sublist of another index 
            // it's irrelevant
            // not quite sure about the included columns issue yet
            // if every key in this index was found in the corresponding position in the other index
            // its a problem
            if (TheseKeysColumns.Count      == TheOtherKeysColumns.Count
                && allPos.Count             == matchedPos.Count 
                && matchedPos.Count         > 0 
                && matchedPos.Count - 1     == matchedPos.Max())
            {
                foundIndexThatMatchesAKey = true;
            }
            else if (TheseKeysColumns.Count      < TheOtherKeysColumns.Count
                //everything returned was a match
                && allPos.Count                 == matchedPos.Count
                //there was a match
                && matchedPos.Count > 0
                //everything matched matched matched in the first n columns
                && matchedPos.Count - 1 == matchedPos.Max()
                )
            {
                bool matchedOneForOne = true;
                //for (int i = 0; i < allPos.Count; i++)
                //{
                //    if ( allPos[i] != i) {
                //        matchedOneForOne = false ;
                //        break ;
                //    }
                //}
                foundIndexThatMatchesAKey = matchedOneForOne;
            }

            return foundIndexThatMatchesAKey;
        }

        #endregion    
 
    }
}
